using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using Oracle.ManagedDataAccess.Client;
using System.Diagnostics;
using System.Data;

namespace Lzfy_His_Service.Repositories
{
    public class RefundTaskDetailRepository : IRefundTaskDetailRepository
    {
        private readonly IOracleConnectionFactory _connectionFactory;
        private readonly ILogger<RefundTaskDetailRepository> _logger;
        private readonly ConnectionLeakDetector _leakDetector;

        public RefundTaskDetailRepository(
            IOracleConnectionFactory connectionFactory,
            ILogger<RefundTaskDetailRepository> logger,
            ConnectionLeakDetector leakDetector)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
            _leakDetector = leakDetector;
        }

        public async Task<RefundTaskDetail?> GetByIdAsync(long id)
        {
            var operationId = Guid.NewGuid().ToString("N")[..8];
            var stopwatch = Stopwatch.StartNew();
            
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                if (connection is not Oracle.ManagedDataAccess.Client.OracleConnection oracleConnection)
                {
                    throw new InvalidOperationException($"期望Oracle连接，但得到{connection.GetType().Name}");
                }
                using var trackedConnection = oracleConnection.TrackConnection(_leakDetector, $"RefundTaskDetailRepository.GetByIdAsync({id})", operationId);
                
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           MASTER_ID as MasterId, 
                           PATIENT_ID as PatientId, 
                           ACCOUNT_ID as AccountId, 
                           ACCOUNT_INDEX as AccountIndex, 
                           BALANCE as Balance, 
                           CONTACT_COUNT as ContactCount, 
                           CONTACT_TIME1 as ContactTime1, 
                           CONTACT_TIME2 as ContactTime2, 
                           CONTACT_TIME3 as ContactTime3, 
                           CONTACT_TIME4 as ContactTime4, 
                           CONTACT_TIME5 as ContactTime5,
                           PHONE_NUMBER as PhoneNumber, 
                           PAT_NAME as PatName, 
                           ID_NUMBER as IdNumber, 
                           SEX as Sex, 
                           NEXT_OF_KIN as NextOfKin, 
                           NEXT_OF_KIN_PHONE as NextOfKinPhone,
                           GUARDIAN_NAME as GuardianName, 
                           GUARDIAN_IDCODE as GuardianIdcode, 
                           NOTES1 as Notes1, 
                           NOTES2 as Notes2, 
                           NOTES3 as Notes3, 
                           NOTES4 as Notes4, 
                           NOTES5 as Notes5,
                           USER_NOTES1 as UserNotes1, 
                           USER_NOTES2 as UserNotes2, 
                           USER_NOTES3 as UserNotes3, 
                           USER_NOTES4 as UserNotes4, 
                           USER_NOTES5 as UserNotes5
                    FROM PCA.LZFY_REFUND_TASK_DETAIL
                    WHERE ID = :id";

                var result = await connection.QueryFirstOrDefaultAsync<RefundTaskDetail>(sql, new { id });
                
                _logger.LogInformation("Oracle查询成功: {Operation} {Duration}ms {ConnectionId}", 
                    $"GetByIdAsync({id})", stopwatch.ElapsedMilliseconds, operationId);
                    
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task detail by id: {Id} (Operation: {OperationId})", id, operationId);
                throw;
            }
        }

        public async Task<IEnumerable<RefundTaskDetail>> GetAllAsync()
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           MASTER_ID as MasterId, 
                           PATIENT_ID as PatientId, 
                           ACCOUNT_ID as AccountId, 
                           ACCOUNT_INDEX as AccountIndex, 
                           BALANCE as Balance, 
                           CONTACT_COUNT as ContactCount, 
                           CONTACT_TIME1 as ContactTime1, 
                           CONTACT_TIME2 as ContactTime2, 
                           CONTACT_TIME3 as ContactTime3, 
                           CONTACT_TIME4 as ContactTime4, 
                           CONTACT_TIME5 as ContactTime5,
                           PHONE_NUMBER as PhoneNumber, 
                           PAT_NAME as PatName, 
                           ID_NUMBER as IdNumber, 
                           SEX as Sex, 
                           NEXT_OF_KIN as NextOfKin, 
                           NEXT_OF_KIN_PHONE as NextOfKinPhone,
                           GUARDIAN_NAME as GuardianName, 
                           GUARDIAN_IDCODE as GuardianIdcode, 
                           NOTES1 as Notes1, 
                           NOTES2 as Notes2, 
                           NOTES3 as Notes3, 
                           NOTES4 as Notes4, 
                           NOTES5 as Notes5,
                           USER_NOTES1 as UserNotes1, 
                           USER_NOTES2 as UserNotes2, 
                           USER_NOTES3 as UserNotes3, 
                           USER_NOTES4 as UserNotes4, 
                           USER_NOTES5 as UserNotes5
                    FROM PCA.LZFY_REFUND_TASK_DETAIL
                    ORDER BY INPUT_DATE DESC";

                return await connection.QueryAsync<RefundTaskDetail>(sql);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting all refund task details");
                throw;
            }
        }

        public async Task<IEnumerable<RefundTaskDetail>> GetByMasterIdAsync(long masterId)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           MASTER_ID as MasterId, 
                           PATIENT_ID as PatientId, 
                           ACCOUNT_ID as AccountId, 
                           ACCOUNT_INDEX as AccountIndex, 
                           BALANCE as Balance, 
                           CONTACT_COUNT as ContactCount, 
                           CONTACT_TIME1 as ContactTime1, 
                           CONTACT_TIME2 as ContactTime2, 
                           CONTACT_TIME3 as ContactTime3, 
                           CONTACT_TIME4 as ContactTime4, 
                           CONTACT_TIME5 as ContactTime5,
                           PHONE_NUMBER as PhoneNumber, 
                           PAT_NAME as PatName, 
                           ID_NUMBER as IdNumber, 
                           SEX as Sex, 
                           NEXT_OF_KIN as NextOfKin, 
                           NEXT_OF_KIN_PHONE as NextOfKinPhone,
                           GUARDIAN_NAME as GuardianName, 
                           GUARDIAN_IDCODE as GuardianIdcode, 
                           NOTES1 as Notes1, 
                           NOTES2 as Notes2, 
                           NOTES3 as Notes3, 
                           NOTES4 as Notes4, 
                           NOTES5 as Notes5,
                           USER_NOTES1 as UserNotes1, 
                           USER_NOTES2 as UserNotes2, 
                           USER_NOTES3 as UserNotes3, 
                           USER_NOTES4 as UserNotes4, 
                           USER_NOTES5 as UserNotes5
                    FROM PCA.LZFY_REFUND_TASK_DETAIL
                    WHERE MASTER_ID = :masterId
                    ORDER BY INPUT_DATE DESC";

                return await connection.QueryAsync<RefundTaskDetail>(sql, new { masterId });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task details by master id: {MasterId}", masterId);
                throw;
            }
        }

        public async Task<(IEnumerable<RefundTaskDetail> data, int totalCount)> GetWithFilterAsync(
            long? masterId = null,
            string? patientId = null,
            string? patName = null,
            string? phoneNumber = null,
            int? contactCount = null,
            DateTime? startDate = null,
            DateTime? endDate = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();

                if (masterId.HasValue)
                {
                    whereConditions.Add("MASTER_ID = :masterId");
                    parameters.Add("masterId", masterId.Value);
                }

                if (!string.IsNullOrEmpty(patientId))
                {
                    whereConditions.Add("UPPER(PATIENT_ID) LIKE UPPER(:patientId)");
                    parameters.Add("patientId", $"%{patientId}%");
                }

                if (!string.IsNullOrEmpty(patName))
                {
                    whereConditions.Add("UPPER(PAT_NAME) LIKE UPPER(:patName)");
                    parameters.Add("patName", $"%{patName}%");
                }

                if (!string.IsNullOrEmpty(phoneNumber))
                {
                    whereConditions.Add("PHONE_NUMBER LIKE :phoneNumber");
                    parameters.Add("phoneNumber", $"%{phoneNumber}%");
                }

                if (contactCount.HasValue)
                {
                    whereConditions.Add("CONTACT_COUNT = :contactCount");
                    parameters.Add("contactCount", contactCount.Value);
                }

                if (startDate.HasValue)
                {
                    whereConditions.Add("INPUT_DATE >= :startDate");
                    parameters.Add("startDate", startDate.Value);
                }

                if (endDate.HasValue)
                {
                    whereConditions.Add("INPUT_DATE <= :endDate");
                    parameters.Add("endDate", endDate.Value);
                }

                var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // Count query
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM PCA.LZFY_REFUND_TASK_DETAIL
                    {whereClause}";

                var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

                // Data query with pagination
                var offset = (pageIndex - 1) * pageSize;
                parameters.Add("offset", offset);
                parameters.Add("pageSize", pageSize);

                var dataSql = $@"
                    SELECT * FROM (
                        SELECT ID as Id, 
                               INPUT_DATE as InputDate, 
                               MASTER_ID as MasterId, 
                               PATIENT_ID as PatientId, 
                               ACCOUNT_ID as AccountId, 
                               ACCOUNT_INDEX as AccountIndex, 
                               BALANCE as Balance, 
                               CONTACT_COUNT as ContactCount, 
                               CONTACT_TIME1 as ContactTime1, 
                               CONTACT_TIME2 as ContactTime2, 
                               CONTACT_TIME3 as ContactTime3, 
                               CONTACT_TIME4 as ContactTime4, 
                               CONTACT_TIME5 as ContactTime5,
                               PHONE_NUMBER as PhoneNumber, 
                               PAT_NAME as PatName, 
                               ID_NUMBER as IdNumber, 
                               SEX as Sex, 
                               NEXT_OF_KIN as NextOfKin, 
                               NEXT_OF_KIN_PHONE as NextOfKinPhone,
                               GUARDIAN_NAME as GuardianName, 
                               GUARDIAN_IDCODE as GuardianIdcode, 
                               NOTES1 as Notes1, 
                               NOTES2 as Notes2, 
                               NOTES3 as Notes3, 
                               NOTES4 as Notes4, 
                               NOTES5 as Notes5,
                               USER_NOTES1 as UserNotes1, 
                               USER_NOTES2 as UserNotes2, 
                               USER_NOTES3 as UserNotes3, 
                               USER_NOTES4 as UserNotes4, 
                               USER_NOTES5 as UserNotes5,
                               ROW_NUMBER() OVER (ORDER BY INPUT_DATE DESC) AS rn
                        FROM PCA.LZFY_REFUND_TASK_DETAIL
                        {whereClause}
                    ) WHERE rn > :offset AND rn <= :offset + :pageSize";

                var data = await connection.QueryAsync<RefundTaskDetail>(dataSql, parameters);

                return (data, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task details with filter");
                throw;
            }
        }

        public async Task<IEnumerable<RefundTaskDetail>> GetByPatientIdAsync(string patientId)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           MASTER_ID as MasterId, 
                           PATIENT_ID as PatientId, 
                           ACCOUNT_ID as AccountId, 
                           ACCOUNT_INDEX as AccountIndex, 
                           BALANCE as Balance, 
                           CONTACT_COUNT as ContactCount, 
                           CONTACT_TIME1 as ContactTime1, 
                           CONTACT_TIME2 as ContactTime2, 
                           CONTACT_TIME3 as ContactTime3, 
                           CONTACT_TIME4 as ContactTime4, 
                           CONTACT_TIME5 as ContactTime5,
                           PHONE_NUMBER as PhoneNumber, 
                           PAT_NAME as PatName, 
                           ID_NUMBER as IdNumber, 
                           SEX as Sex, 
                           NEXT_OF_KIN as NextOfKin, 
                           NEXT_OF_KIN_PHONE as NextOfKinPhone,
                           GUARDIAN_NAME as GuardianName, 
                           GUARDIAN_IDCODE as GuardianIdcode, 
                           NOTES1 as Notes1, 
                           NOTES2 as Notes2, 
                           NOTES3 as Notes3, 
                           NOTES4 as Notes4, 
                           NOTES5 as Notes5,
                           USER_NOTES1 as UserNotes1, 
                           USER_NOTES2 as UserNotes2, 
                           USER_NOTES3 as UserNotes3, 
                           USER_NOTES4 as UserNotes4, 
                           USER_NOTES5 as UserNotes5
                    FROM PCA.LZFY_REFUND_TASK_DETAIL
                    WHERE PATIENT_ID = :patientId
                    ORDER BY INPUT_DATE DESC";

                return await connection.QueryAsync<RefundTaskDetail>(sql, new { patientId });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task details by patient id: {PatientId}", patientId);
                throw;
            }
        }

        public async Task<bool> ExistsByAccountIdAsync(string? accountId, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                const string sql = "SELECT COUNT(1) FROM PCA.LZFY_REFUND_TASK_DETAIL WHERE ACCOUNT_ID = :accountId";
                var count = await connection.ExecuteScalarAsync<int>(sql, new { accountId }, transaction: transaction);
                return count > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error checking if account id exists: {AccountId}", accountId);
                throw;
            }
        }

        public async Task<long> CreateAsync(RefundTaskDetail refundTaskDetail, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                // 检查account_id是否已存在
                var exists = await ExistsByAccountIdAsync(refundTaskDetail.AccountId, connection, transaction);
                if (exists)
                {
                    throw new InvalidOperationException($"Account ID '{refundTaskDetail.AccountId}' already exists in refund task details");
                }

                const string sql = @"
                    INSERT INTO PCA.LZFY_REFUND_TASK_DETAIL 
                    (MASTER_ID, PATIENT_ID, ACCOUNT_ID, ACCOUNT_INDEX, BALANCE, 
                     CONTACT_COUNT, PHONE_NUMBER, PAT_NAME, ID_NUMBER, SEX, 
                     NEXT_OF_KIN, NEXT_OF_KIN_PHONE, GUARDIAN_NAME, GUARDIAN_IDCODE, 
                     NOTES1, NOTES2, NOTES3, NOTES4, NOTES5, 
                     USER_NOTES1, USER_NOTES2, USER_NOTES3, USER_NOTES4, USER_NOTES5,
                     CONTACT_TIME1, CONTACT_TIME2, CONTACT_TIME3, CONTACT_TIME4, CONTACT_TIME5)
                    VALUES (:masterId, :patientId, :accountId, :accountIndex, :balance, 
                            :contactCount, :phoneNumber, :patName, :idNumber, :sex, 
                            :nextOfKin, :nextOfKinPhone, :guardianName, :guardianIdcode, 
                            :notes1, :notes2, :notes3, :notes4, :notes5, 
                            :userNotes1, :userNotes2, :userNotes3, :userNotes4, :userNotes5,
                            :contactTime1, :contactTime2, :contactTime3, :contactTime4, :contactTime5)
                    RETURNING ID INTO :id";

                var parameters = new DynamicParameters();
                parameters.Add("masterId", refundTaskDetail.MasterId);
                parameters.Add("patientId", refundTaskDetail.PatientId);
                parameters.Add("accountId", refundTaskDetail.AccountId);
                parameters.Add("accountIndex", refundTaskDetail.AccountIndex);
                parameters.Add("balance", refundTaskDetail.Balance);
                parameters.Add("contactCount", refundTaskDetail.ContactCount);
                parameters.Add("phoneNumber", refundTaskDetail.PhoneNumber);
                parameters.Add("patName", refundTaskDetail.PatName);
                parameters.Add("idNumber", refundTaskDetail.IdNumber);
                parameters.Add("sex", refundTaskDetail.Sex);
                parameters.Add("nextOfKin", refundTaskDetail.NextOfKin);
                parameters.Add("nextOfKinPhone", refundTaskDetail.NextOfKinPhone);
                parameters.Add("guardianName", refundTaskDetail.GuardianName);
                parameters.Add("guardianIdcode", refundTaskDetail.GuardianIdcode);
                parameters.Add("notes1", refundTaskDetail.Notes1);
                parameters.Add("notes2", refundTaskDetail.Notes2);
                parameters.Add("notes3", refundTaskDetail.Notes3);
                parameters.Add("notes4", refundTaskDetail.Notes4);
                parameters.Add("notes5", refundTaskDetail.Notes5);
                parameters.Add("userNotes1", refundTaskDetail.UserNotes1);
                parameters.Add("userNotes2", refundTaskDetail.UserNotes2);
                parameters.Add("userNotes3", refundTaskDetail.UserNotes3);
                parameters.Add("userNotes4", refundTaskDetail.UserNotes4);
                parameters.Add("userNotes5", refundTaskDetail.UserNotes5);
                parameters.Add("contactTime1", refundTaskDetail.ContactTime1);
                parameters.Add("contactTime2", refundTaskDetail.ContactTime2);
                parameters.Add("contactTime3", refundTaskDetail.ContactTime3);
                parameters.Add("contactTime4", refundTaskDetail.ContactTime4);
                parameters.Add("contactTime5", refundTaskDetail.ContactTime5);
                parameters.Add("id", dbType: DbType.Int64, direction: ParameterDirection.Output);

                await connection.ExecuteAsync(sql, parameters, transaction: transaction);

                return parameters.Get<long>("id");
            }
            catch (OracleException oracleEx) when (oracleEx.Number == 1) // ORA-00001: 违反唯一约束条件
            {
                _logger.LogWarning(oracleEx, "Unique constraint violation for account ID: {AccountId}", refundTaskDetail.AccountId);
                throw new InvalidOperationException($"Account ID '{refundTaskDetail.AccountId}' already exists in refund task details", oracleEx);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error creating refund task detail with transaction");
                throw;
            }
        }

        public async Task<bool> UpdateAsync(RefundTaskDetail refundTaskDetail, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                const string sql = @"
                    UPDATE PCA.LZFY_REFUND_TASK_DETAIL 
                    SET MASTER_ID = :masterId,
                        PATIENT_ID = :patientId,
                        ACCOUNT_ID = :accountId,
                        ACCOUNT_INDEX = :accountIndex,
                        BALANCE = :balance,
                        CONTACT_COUNT = :contactCount,
                        PHONE_NUMBER = :phoneNumber,
                        PAT_NAME = :patName,
                        ID_NUMBER = :idNumber,
                        SEX = :sex,
                        NEXT_OF_KIN = :nextOfKin,
                        NEXT_OF_KIN_PHONE = :nextOfKinPhone,
                        GUARDIAN_NAME = :guardianName,
                        GUARDIAN_IDCODE = :guardianIdcode,
                        NOTES1 = :notes1,
                        NOTES2 = :notes2,
                        NOTES3 = :notes3,
                        NOTES4 = :notes4,
                        NOTES5 = :notes5,
                        USER_NOTES1 = :userNotes1,
                        USER_NOTES2 = :userNotes2,
                        USER_NOTES3 = :userNotes3,
                        USER_NOTES4 = :userNotes4,
                        USER_NOTES5 = :userNotes5,
                        CONTACT_TIME1 = :contactTime1,
                        CONTACT_TIME2 = :contactTime2,
                        CONTACT_TIME3 = :contactTime3,
                        CONTACT_TIME4 = :contactTime4,
                        CONTACT_TIME5 = :contactTime5
                    WHERE ID = :id";

                var rowsAffected = await connection.ExecuteAsync(sql, new
                {
                    id = refundTaskDetail.Id,
                    masterId = refundTaskDetail.MasterId,
                    patientId = refundTaskDetail.PatientId,
                    accountId = refundTaskDetail.AccountId,
                    accountIndex = refundTaskDetail.AccountIndex,
                    balance = refundTaskDetail.Balance,
                    contactCount = refundTaskDetail.ContactCount,
                    phoneNumber = refundTaskDetail.PhoneNumber,
                    patName = refundTaskDetail.PatName,
                    idNumber = refundTaskDetail.IdNumber,
                    sex = refundTaskDetail.Sex,
                    nextOfKin = refundTaskDetail.NextOfKin,
                    nextOfKinPhone = refundTaskDetail.NextOfKinPhone,
                    guardianName = refundTaskDetail.GuardianName,
                    guardianIdcode = refundTaskDetail.GuardianIdcode,
                    notes1 = refundTaskDetail.Notes1,
                    notes2 = refundTaskDetail.Notes2,
                    notes3 = refundTaskDetail.Notes3,
                    notes4 = refundTaskDetail.Notes4,
                    notes5 = refundTaskDetail.Notes5,
                    userNotes1 = refundTaskDetail.UserNotes1,
                    userNotes2 = refundTaskDetail.UserNotes2,
                    userNotes3 = refundTaskDetail.UserNotes3,
                    userNotes4 = refundTaskDetail.UserNotes4,
                    userNotes5 = refundTaskDetail.UserNotes5,
                    contactTime1 = refundTaskDetail.ContactTime1,
                    contactTime2 = refundTaskDetail.ContactTime2,
                    contactTime3 = refundTaskDetail.ContactTime3,
                    contactTime4 = refundTaskDetail.ContactTime4,
                    contactTime5 = refundTaskDetail.ContactTime5
                }, transaction: transaction);

                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating refund task detail with transaction: {Id}", refundTaskDetail.Id);
                throw;
            }
        }

        public async Task<bool> BatchCreateAsync(IEnumerable<RefundTaskDetail> refundTaskDetails, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                // 检查所有account_id是否已存在（逐条检查避免Oracle IN子句语法问题）
                var accountIds = refundTaskDetails.Select(d => d.AccountId).Distinct().ToList();
                var existingAccountIds = new List<string>();
                
                foreach (var accountId in accountIds)
                {
                    const string checkSql = "SELECT ACCOUNT_ID FROM PCA.LZFY_REFUND_TASK_DETAIL WHERE ACCOUNT_ID = :accountId";
                    var existing = await connection.QueryFirstOrDefaultAsync<string>(checkSql, new { accountId }, transaction: transaction);
                    if (!string.IsNullOrEmpty(existing))
                    {
                        existingAccountIds.Add(existing);
                    }
                }
                
                if (existingAccountIds.Any())
                {
                    throw new InvalidOperationException($"Account IDs already exist: {string.Join(", ", existingAccountIds)}");
                }

                const string sql = @"
                    INSERT INTO PCA.LZFY_REFUND_TASK_DETAIL 
                    (MASTER_ID, PATIENT_ID, ACCOUNT_ID, ACCOUNT_INDEX, BALANCE, 
                     CONTACT_COUNT, PHONE_NUMBER, PAT_NAME, ID_NUMBER, SEX, 
                     NEXT_OF_KIN, NEXT_OF_KIN_PHONE, GUARDIAN_NAME, GUARDIAN_IDCODE, 
                     NOTES1, NOTES2, NOTES3, NOTES4, NOTES5, 
                     USER_NOTES1, USER_NOTES2, USER_NOTES3, USER_NOTES4, USER_NOTES5,
                     CONTACT_TIME1, CONTACT_TIME2, CONTACT_TIME3, CONTACT_TIME4, CONTACT_TIME5)
                    VALUES (:masterId, :patientId, :accountId, :accountIndex, :balance, 
                            :contactCount, :phoneNumber, :patName, :idNumber, :sex, 
                            :nextOfKin, :nextOfKinPhone, :guardianName, :guardianIdcode, 
                            :notes1, :notes2, :notes3, :notes4, :notes5, 
                            :userNotes1, :userNotes2, :userNotes3, :userNotes4, :userNotes5,
                            :contactTime1, :contactTime2, :contactTime3, :contactTime4, :contactTime5)";

                var rowsAffected = await connection.ExecuteAsync(sql, refundTaskDetails.Select(d => new
                {
                    masterId = d.MasterId,
                    patientId = d.PatientId,
                    accountId = d.AccountId,
                    accountIndex = d.AccountIndex,
                    balance = d.Balance,
                    contactCount = d.ContactCount,
                    phoneNumber = d.PhoneNumber,
                    patName = d.PatName,
                    idNumber = d.IdNumber,
                    sex = d.Sex,
                    nextOfKin = d.NextOfKin,
                    nextOfKinPhone = d.NextOfKinPhone,
                    guardianName = d.GuardianName,
                    guardianIdcode = d.GuardianIdcode,
                    notes1 = d.Notes1,
                    notes2 = d.Notes2,
                    notes3 = d.Notes3,
                    notes4 = d.Notes4,
                    notes5 = d.Notes5,
                    userNotes1 = d.UserNotes1,
                    userNotes2 = d.UserNotes2,
                    userNotes3 = d.UserNotes3,
                    userNotes4 = d.UserNotes4,
                    userNotes5 = d.UserNotes5,
                    contactTime1 = d.ContactTime1,
                    contactTime2 = d.ContactTime2,
                    contactTime3 = d.ContactTime3,
                    contactTime4 = d.ContactTime4,
                    contactTime5 = d.ContactTime5
                }), transaction: transaction);

                return rowsAffected > 0;
            }
            catch (OracleException oracleEx) when (oracleEx.Number == 1) // ORA-00001: 违反唯一约束条件
            {
                _logger.LogWarning(oracleEx, "Unique constraint violation during batch create");
                throw new InvalidOperationException("One or more account IDs already exist in refund task details", oracleEx);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error batch creating refund task details with transaction");
                throw;
            }
        }

        public async Task<bool> DeleteAsync(long id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = "DELETE FROM PCA.LZFY_REFUND_TASK_DETAIL WHERE ID = :id";

                var rowsAffected = await connection.ExecuteAsync(sql, new { id });
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error deleting refund task detail: {Id}", id);
                throw;
            }
        }

        public async Task<bool> DeleteByMasterIdAsync(long masterId)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = "DELETE FROM PCA.LZFY_REFUND_TASK_DETAIL WHERE MASTER_ID = :masterId";

                var rowsAffected = await connection.ExecuteAsync(sql, new { masterId });
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error deleting refund task details by master id: {MasterId}", masterId);
                throw;
            }
        }

        public async Task<long> CreateAsync(RefundTaskDetail refundTaskDetail)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                return await CreateAsync(refundTaskDetail, connection);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error creating refund task detail");
                throw;
            }
        }

        public async Task<bool> UpdateAsync(RefundTaskDetail refundTaskDetail)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                return await UpdateAsync(refundTaskDetail, connection);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating refund task detail: {Id}", refundTaskDetail.Id);
                throw;
            }
        }

        public async Task<bool> BatchCreateAsync(IEnumerable<RefundTaskDetail> refundTaskDetails)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                using var transaction = connection.BeginTransaction();
                try
                {
                    var result = await BatchCreateAsync(refundTaskDetails, connection, transaction);
                    transaction.Commit();
                    return result;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error batch creating refund task details");
                throw;
            }
        }

        public async Task<bool> UpdateContactTimeAsync(long id, DateTime contactTime)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                
                // First get current contact count
                const string getContactCountSql = "SELECT CONTACT_COUNT FROM PCA.LZFY_REFUND_TASK_DETAIL WHERE ID = :id";
                var currentContactCount = await connection.QuerySingleOrDefaultAsync<int?>(getContactCountSql, new { id }) ?? 0;
                
                var newContactCount = currentContactCount + 1;
                var contactTimeColumn = $"CONTACT_TIME{newContactCount}";
                
                // Only update if we haven't exceeded 5 contacts
                if (newContactCount <= 5)
                {
                    var sql = $@"
                        UPDATE PCA.LZFY_REFUND_TASK_DETAIL
                        SET CONTACT_COUNT = :contactCount,
                            {contactTimeColumn} = :contactTime
                        WHERE ID = :id";

                    var rowsAffected = await connection.ExecuteAsync(sql, new
                    {
                        id,
                        contactCount = newContactCount,
                        contactTime
                    });

                    return rowsAffected > 0;
                }

                return false;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating contact time for refund task detail: {Id}", id);
                throw;
            }
        }

        

        public async Task<IEnumerable<RefundTaskDetail>> GetByMasterIdsAsync(IEnumerable<long> masterIds)
        {
            try
            {
                if (masterIds == null || !masterIds.Any())
                {
                    return Enumerable.Empty<RefundTaskDetail>();
                }

                using var connection = _connectionFactory.CreateConnection();
                
                // 创建参数化查询
                var parameters = new DynamicParameters();
                var paramNames = new List<string>();
                
                int index = 0;
                foreach (var id in masterIds)
                {
                    var paramName = $":id{index}";
                    parameters.Add(paramName.TrimStart(':'), id);
                    paramNames.Add(paramName);
                    index++;
                }
                
                var sql = $@"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           MASTER_ID as MasterId, 
                           PATIENT_ID as PatientId, 
                           ACCOUNT_ID as AccountId, 
                           ACCOUNT_INDEX as AccountIndex, 
                           BALANCE as Balance, 
                           CONTACT_COUNT as ContactCount, 
                           CONTACT_TIME1 as ContactTime1, 
                           CONTACT_TIME2 as ContactTime2, 
                           CONTACT_TIME3 as ContactTime3, 
                           CONTACT_TIME4 as ContactTime4, 
                           CONTACT_TIME5 as ContactTime5,
                           PHONE_NUMBER as PhoneNumber, 
                           PAT_NAME as PatName, 
                           ID_NUMBER as IdNumber, 
                           SEX as Sex, 
                           NEXT_OF_KIN as NextOfKin, 
                           NEXT_OF_KIN_PHONE as NextOfKinPhone,
                           GUARDIAN_NAME as GuardianName, 
                           GUARDIAN_IDCODE as GuardianIdcode, 
                           NOTES1 as Notes1, 
                           NOTES2 as Notes2, 
                           NOTES3 as Notes3, 
                           NOTES4 as Notes4, 
                           NOTES5 as Notes5
                    FROM pca.lzfy_refund_task_detail 
                    WHERE MASTER_ID IN @MasterIds
                    ORDER BY MASTER_ID, ACCOUNT_INDEX";

                return await connection.QueryAsync<RefundTaskDetail>(sql, new { MasterIds = masterIds });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task details by master ids");
                throw;
            }
        }

        public async Task<IEnumerable<RefundTaskDetail>> GetHisRefundDataAsync(int? maxCount = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    select tb1.patient_id,
                           tb1.account_id,
                           tb1.account_index,
                           tb1.balance,
                           tb1.phone_number,
                           tb1.pat_name,
                           tb1.id_number,
                           tb1.sex,
                           tb1.next_of_kin,
                           tb1.next_of_kin_phone,
                           tb1.guardian_name,
                           tb1.guardian_idcode
                      from (select t3.patient_id,
                                   t1.account_id,
                                   t1.account_index,
                                   t1.balance,
                                   t3.phone_number1 as phone_number,
                                   t3.name as pat_name,
                                   t3.id_number,
                                   decode(t3.sex_code, '1', '男', '2', '女') as sex,
                                   t3.next_of_kin,
                                   t3.next_of_kin_phone1 as next_of_kin_phone,
                                   t3.guardian_name,
                                   t3.guardian_idcode
                              from pca.pca_prepay_account_master t1
                              join pca.pca_prepay_account_index t2
                                on t1.account_index = t2.account_index
                               and t1.account_type = 'O'
                               and t1.latest_trans_time_new < to_date('2025-03-27','yyyy-mm-dd')
                              join pca.pca_patient_info t3
                                on t2.patient_id = t3.patient_id
                             where t1.balance >= 90
                               and not exists (select 1
                                      from pca.lzfy_refund_task_detail t4
                                     where t1.account_id = t4.account_id)
                               and t3.phone_number1 is not null
                               and t3.phone_number1 <> '18888888888'
                             order by t1.balance desc
                            ) tb1
                     where rownum <= :MaxCount";

                var parameters = new { MaxCount = maxCount ?? 100 };

                var results = await connection.QueryAsync(sql, parameters);
                
                return results.Select(r => new RefundTaskDetail
                {
                    PatientId = r.PATIENT_ID?.ToString(),
                    AccountId = r.ACCOUNT_ID?.ToString(),
                    AccountIndex = r.ACCOUNT_INDEX?.ToString(),
                    Balance = Convert.ToDecimal(r.BALANCE ?? 0),
                    PhoneNumber = r.PHONE_NUMBER?.ToString(),
                    PatName = r.PAT_NAME?.ToString(),
                    IdNumber = r.ID_NUMBER?.ToString(),
                    Sex = r.SEX?.ToString(),
                    NextOfKin = r.NEXT_OF_KIN?.ToString(),
                    NextOfKinPhone = r.NEXT_OF_KIN_PHONE?.ToString(),
                    GuardianName = r.GUARDIAN_NAME?.ToString(),
                    GuardianIdcode = r.GUARDIAN_IDCODE?.ToString(),
                    ContactCount = 0
                });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting HIS refund data");
                throw;
            }
        }
    }
}